#!/usr/bin/env python3
"""
Database Setup Script
=====================

Script to initialize the database and create tables.
"""

import os
import sys
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
import logging

# Add project root to path
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def create_database_if_not_exists(config):
    """Create database if it doesn't exist."""
    try:
        # Connect to MySQL server (without database)
        engine = create_engine(
            f"mysql+pymysql://{config['username']}:{config['password']}"
            f"@{config['host']}:{config['port']}",
            isolation_level="AUTOCOMMIT"
        )
        
        with engine.connect() as conn:
            # Check if database exists
            result = conn.execute(
                text("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = :db_name"),
                {"db_name": config['database']}
            )
            
            if not result.fetchone():
                # Create database
                conn.execute(text(f"CREATE DATABASE {config['database']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"))
                logger.info(f"Database '{config['database']}' created successfully")
            else:
                logger.info(f"Database '{config['database']}' already exists")
        
        return True
    
    except SQLAlchemyError as e:
        logger.error(f"Database creation error: {str(e)}")
        return False

def setup_database():
    """Set up the database."""
    # Load configuration
    from config import DevelopmentConfig
    
    config = {
        'host': DevelopmentConfig.MYSQL_HOST,
        'port': DevelopmentConfig.MYSQL_PORT,
        'username': DevelopmentConfig.MYSQL_USERNAME,
        'password': DevelopmentConfig.MYSQL_PASSWORD,
        'database': DevelopmentConfig.MYSQL_DATABASE
    }
    
    logger.info("Starting database setup...")
    
    # Create database
    if not create_database_if_not_exists(config):
        logger.error("Failed to create database")
        return False
    
    # Create Flask app to initialize database
    from app import create_app
    from app import db
    
    app = create_app('development')
    
    with app.app_context():
        try:
            # Create all tables
            db.create_all()
            logger.info("Database tables created successfully")
            
            return True
        
        except SQLAlchemyError as e:
            logger.error(f"Table creation error: {str(e)}")
            return False

if __name__ == '__main__':
    success = setup_database()
    if success:
        logger.info("Database setup completed successfully")
        sys.exit(0)
    else:
        logger.error("Database setup failed")
        sys.exit(1)